package demo.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DBUtil {
	static final String driver="org.apache.derby.jdbc.EmbededDriver";
	//static final String driver="org.apache.derby.jdbc.ClientDriver";
	
	static final String url="jdbc:derby:";
	
	static final String dbName="library";
	
	public static int createDB() {
		Connection con = null;
		if(!dbExists()) {
			try {
				con = DriverManager.getConnection(url + dbName + ";create=true");
				
				String createUsersTableSqlStr = "CREATE TABLE users(id  varchar(40) not null,password varchar(20) not null,NAME VARCHAR(20) NOT NULL,SEX VARCHAR(20) NOT NULL,BIRTH DATE,PRO VARCHAR(20) NOT NULL,GRADE VARCHAR(20) NOT NULL,TEL DECIMAL(11,0) NOT NULL,EMAIL VARCHAR(20) NOT NULL,USER_RIGHT VARCHAR(1) DEFAULT '0')";
				String createAdminTableSqlStr ="CREATE TABLE admin(ID VARCHAR(20) NOT NULL,NAME VARCHAR(20) NOT NULL,PASSWORD VARCHAR(20) NOT NULL,TEL VARCHAR(20) NOT NULL)";
				String createBookTableSqlStr = "CREATE TABLE BOOK (ID INTEGER NOT NULL,NAME VARCHAR(100) NOT NULL,AUTHOR VARCHAR(100) NOT NULL,PUBLISH VARCHAR(100),PUBDATE DATE,PRICE DECIMAL(7,2) NOT NULL,NUM INTEGER NOT NULL,SORT VARCHAR(100) NOT NULL,RECORD DATE,ISBN VARCHAR(17))";
				String createBorrowTableSqlStr = "CREATE TABLE BORROW (USER_ID DECIMAL(12,0) NOT NULL,BOOK_ID INTEGER NOT NULL,BRROW_DATE DATE NOT NULL,DUE DATE default '0')";
				String createPreconcertTableSqlStr = "CREATE TABLE PRECONCERT (USER_ID DECIMAL(12,0) NOT NULL,BOOK_ID INTEGER NOT NULL,PREDATE DATE NOT NULL)";
				String createReturnTableSqlStr = "CREATE TABLE RETURN (USER_ID DECIMAL(12,0) NOT NULL,BOOK_ID INTEGER NOT NULL,BORROW_DATE DATE NOT NULL,RETURN_DATE DATE NOT NULL)";
				String createSortTableSqlStr = "CREATE TABLE SORT (ID VARCHAR(20) NOT NULL,NAME VARCHAR(100) NOT NULL)";
				String createTicketTableSqlStr = "CREATE TABLE  TICKET (USER_ID DECIMAL(12,0) NOT NULL,BOOK_ID INTEGER NOT NULL,DAYS INTEGER NOT NULL,OVERDATE INTEGER,DAMAGE VARCHAR(20) NOT NULL,FEE DOUBLE)";
				
				String insertUsersSqlStr = "INSERT INTO users(id,password,name,sex,birth,pro,grade,tel,email) VALUES('202025710125','202cb962ac59075b964b07152d234b70','呃叻','女',2002-01-22,'信管','20',19898027664,'1240890332@qq.com'),('202012340101','202cb962ac59075b964b07152d234b70','yan',2002-03-06,'中文','20',12345678909,'12345@qq.com'),('202012340102','202cb962ac59075b964b07152d234b70','盐',2002-03-05,'历史','20',12345678909,'12345@qq.com')，('202012340103','202cb962ac59075b964b07152d234b70','yanyan',2002-06-01,'建筑','20',12345678909,'12345@qq.com')";
				//String insertAdminSqlStr = "INSERT INTO admin(id,name,password,tel) VALUES ('001','一号','12345',12345678901),('002','二号','12345',12345678901),('003','三号','12345',12345678901),('004','四号','12345',12345678901)";
				String insertBookSqlStr = "INSERT INTO book(id,name,author,price,num,sort) VALUES (001,'且听风吟','村上春树',50.0,20,'文学'),(002,'我在等你','余秋雨',50.0,'文学'),(003,'简爱','夏洛蒂·勃朗特',50.0,'文学')";
				String insertBorrowSqlStr = "INSERT INTO borrow(user_id,book_id,brrow_date,due) VALUES (202025710125,001,2021-03-29,2021-06-29),(202012340101,002,2021-04-22,2021-07-22),(202012340102,003,2021-01-30,2021-06-30)";
				String insertPreconcertSqlStr = "INSERT INTO preconcert(user_id,book_id,predate) VALUES (202025710125,002,2021-11-11),(202012340102,004,2021-07-07),(202012340103,001,2021-08-11)";
				String insertReturnSqlStr = "INSERT INTO return(user_id,book_id,borrow_date,return_date) VALUES (202025710125,001,2021-03-29,2021-04-11),(202025710101,002,2021-04-22,2021-09-21)";
				String insertSortSqlStr = "INSERT INTO sort(id,name) VALUES ('I','文学'),('J','艺术')";
				String insertTicketSqlStr = "INSERT INTO ticket(user_id,book_id,days,overDate,damage,fee) VALUES (202025710125,001,100,10,'完好',10.0)";
				
				con.setAutoCommit(false);
				
				processUpdateStatement(createUsersTableSqlStr);
				processUpdateStatement(insertUsersSqlStr);
				processUpdateStatement(createAdminTableSqlStr);
				processUpdateStatement(createBookTableSqlStr);
				processUpdateStatement(insertBookSqlStr);
				processUpdateStatement(createBorrowTableSqlStr);
				processUpdateStatement(insertBorrowSqlStr);
				processUpdateStatement(createPreconcertTableSqlStr);
				processUpdateStatement(insertPreconcertSqlStr);
				processUpdateStatement(createReturnTableSqlStr);
				processUpdateStatement(insertReturnSqlStr);
				processUpdateStatement(createSortTableSqlStr);
				processUpdateStatement(insertSortSqlStr);
				batchInsertAdmin();

				con.commit();
				con.setAutoCommit(true);
				return 1;

			}catch (SQLException bue) {
				try {
					con.rollback();
					printSQLException((SQLException) bue);
				} catch (SQLException se) {
					printSQLException(se);
				}
				return -1;
			//}catch (ClassNotFoundException e) {
			//	System.err.println("JDBC Driver " + driver + " not found in CLASSPATH");
			//	return -1;
			} finally {
				closeConnection(con);
			}
		}
		return 0;
	}
	

	public static Connection getConnection() {
		Connection con;
		try {
			//Class.forName(driver);
			con = DriverManager.getConnection(url + dbName);

		} catch (Exception e) {
			return null;
		}
		return con;
	}
	
	public static void closeConnection(Connection con) {
		try {
			if (con != null && !con.isClosed())
				con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	private static int processUpdateStatement(String sql) throws SQLException {
		Connection con = getConnection();
		if (con != null) {
			Statement stmt = con.createStatement();
			int affectedRows = stmt.executeUpdate(sql);
			closeConnection(con);
			return affectedRows;
		}
		return -1;
	}
	
	private static ResultSet processQueryStatement(String sql) throws SQLException {
		Connection con = getConnection();
		if (con != null) {
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			stmt.close();
			return rs;
		}
		return null;
	}
	
	private static boolean dbExists() {
		boolean exists = false;
		Connection con = getConnection();
		if (con != null)
			exists = true;
		closeConnection(con);
		return (exists);
	}
	
	private static void batchInsertAdmin() throws SQLException {
		String insertStudentsSqlStr = "INSERT INTO admins(id, name, password, tel) " + "VALUES(?, ?, ?, ?)";

		String[] numbers = { "001", "002", "003", "004", "005", "006" };

		String[] names = { "张三", "李四", "王五", "赵六", "钱七", "孙八" };

		String[] passwords = { "12345","12345","12345","12345","12345","12345"};

		String[] tels = { "12345678901","12345678909","12345678908","12345678905","09876543213","12345678907" };

		Connection con = getConnection();
		PreparedStatement stmt = con.prepareStatement(insertStudentsSqlStr);

		for (int i = 0; i < numbers.length; i++) {
			stmt.setString(1, numbers[i]);
			stmt.setString(2, names[i]);
			stmt.setString(3, passwords[i]);
			stmt.setString(4, tels[i]);
			stmt.addBatch();
		}

		stmt.executeBatch();
		closeConnection(con);
	}
	
	private static void printSQLException(SQLException e) {
		while (e != null) {
			System.out.print("SQLException: State:   " + e.getSQLState());
			System.out.println("Severity: " + e.getErrorCode());
			System.out.println(e.getMessage());
			e = e.getNextException();
		}
	}
	/*public static void main(String[] args) {
		if(!dbExists()) {
			System.out.println("不存在");
		}
	}*/
}
